﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;

namespace Sunlight.Dispatch.Tools {
    public class SqlServerDbHelper : DbHelper {
        private readonly string connectionString;

        public SqlServerDbHelper(string connectionStr) {
            ParamMark = "@";
            Now = "GetDate()";
            connectionString = connectionStr;
        }

        public override DbConnection CreateDbConnection() {
            return new SqlConnection(connectionString);
        }

        public override DbCommand CreateDbCommand(string commandText, DbConnection dbConnection, DbTransaction ts) {
            return ts == null ? new SqlCommand(commandText, (SqlConnection)dbConnection) : new SqlCommand(commandText, (SqlConnection)dbConnection, (SqlTransaction)ts);
        }

        public override DbParameter CreateDbParameter(string parameterName, object value) {
            return new SqlParameter("@" + parameterName, value);
        }

        public override string GetInsertSql(string tableName, string keyName, string[] fieldNames) {
            return string.Format("insert into {0} ({1}) Values (@{2})", tableName, string.Join(",", fieldNames), string.Join(",@", fieldNames));
        }

        public override string GetUpdateSql(string tableName, string[] modifyFields, string[] conditionFields) {
            var modifyFieldStr = string.Join(",", modifyFields.Select(v => string.Format((string)" {0}=@{0} ", (object)v)).ToArray());
            var conditionFieldStr = string.Join("and", conditionFields.Select(v => string.Format(" {0}=@{0} ", v)).ToArray());
            return string.Format("Update {0} Set {1} Where {2}", tableName, modifyFieldStr, conditionFieldStr);
        }

        public override int ExecuteInsert(DbCommand command, string keyField) {
            if(string.IsNullOrEmpty(keyField))
                return command.ExecuteNonQuery();
            else {
                command.CommandText += " SELECT @@IDENTITY";
                return Convert.ToInt32(command.ExecuteScalar());
            }
        }

        public override DataTable ExecuteDataTable(string sql) {
            var conn = new SqlConnection(connectionString);
            conn.Open();
            var com = conn.CreateCommand();
            com.CommandText = sql;
            var sqlDataAdapter = new SqlDataAdapter(com);
            var dataTable = new DataTable();
            sqlDataAdapter.Fill(dataTable);
            sqlDataAdapter.Dispose();
            conn.Close();
            conn.Dispose();
            return dataTable;
        }

        public override DataTable ExecuteDataTable(string sql, SqlParameter[] sqlParameters) {
            var conn = this.CreateDbConnection() as SqlConnection;
            if(conn == null)
                return null;
            conn.Open();
            var com = conn.CreateCommand();
            com.CommandText = sql;

            foreach(var t in sqlParameters)
                com.Parameters.Add(t);

            var sqlDataAdapter = new SqlDataAdapter(com);

            var dataTable = new DataTable();
            sqlDataAdapter.Fill(dataTable);
            sqlDataAdapter.Dispose();
            com.Parameters.Clear();
            com.Dispose();

            conn.Close();
            conn.Dispose();
            return dataTable;
        }

        public override void ExecuteBulkInsert(string tableName, DataTable dataTable, DbTransaction ts) {
            var sqlConnection = this.CreateDbConnection() as SqlConnection;
            var transaction = ts as SqlTransaction;
            if(sqlConnection == null || transaction == null)
                return;
            var sqlBulkCopy = new SqlBulkCopy(sqlConnection, SqlBulkCopyOptions.Default, transaction);
            sqlBulkCopy.DestinationTableName = tableName;
            sqlBulkCopy.BatchSize = dataTable.Rows.Count;
            sqlBulkCopy.WriteToServer(dataTable);
            sqlBulkCopy.Close();
        }

        public override bool ExecuteTransaction(string sql) {
            var conn = this.CreateDbConnection() as SqlConnection;
            if(conn == null)
                return false;
            conn.Open();
            var sqlTransaction = conn.BeginTransaction();
            try {
                var com = conn.CreateCommand();
                com.Transaction = sqlTransaction;
                com.CommandText = sql;
                com.ExecuteNonQuery();
                com.Dispose();
                sqlTransaction.Commit();
                return true;
            } catch(Exception) {
                sqlTransaction.Rollback();
                return false;
            } finally {
                conn.Close();
                conn.Dispose();
            }
        }

        public override void ExecuteBulkInsert(string tableName, DataTable dataTable) {
            var conn = CreateDbConnection() as SqlConnection;
            if(conn == null)
                return;
            conn.Open();

            var sqlBulkCopy = new SqlBulkCopy(conn);
            sqlBulkCopy.DestinationTableName = tableName;
            sqlBulkCopy.BatchSize = dataTable.Rows.Count;
            sqlBulkCopy.WriteToServer(dataTable);
            sqlBulkCopy.Close();

            conn.Close();
            conn.Dispose();
        }

        public override void QueryDataWithInOperator(string sql, string field, bool isStringField, string[] values, Func<string[], bool> func) {
            var paramNum = values.Length >= PAGE_SIZE ? PAGE_SIZE : values.Length;
            var sqlParams = new string[paramNum];
            for(var i = 0; i < paramNum; i++)
                sqlParams[i] = string.Format("{0}Param{1}", ParamMark, i);

            using(var conn = new SqlConnection(connectionString)) {
                var sqlQuery = string.Concat(sql, sql.IndexOf(" where ", StringComparison.OrdinalIgnoreCase) > -1 ? " and " : " where ", isStringField ? string.Format(" upper({0})", field) : field, " in ({0})");
                var dbParams = new List<SqlParameter>();
                var needStop = false;
                conn.Open();
                for(var i = 0; i < values.Length; i++) {
                    dbParams.Add(new SqlParameter(string.Concat("Param", i % PAGE_SIZE), values[i]));
                    if((i + 1) % PAGE_SIZE == 0) {
                        var command = new SqlCommand(string.Format(sqlQuery, string.Join(",", sqlParams)), conn, null);
                        command.Parameters.AddRange(dbParams.ToArray());
                        var reader = command.ExecuteReader();
                        var result = new string[reader.FieldCount];
                        while(reader.Read()) {
                            for(var index = 0; index < reader.FieldCount; index++)
                                result[index] = reader[index].ToString();
                            needStop = func(result);
                            if(needStop)
                                break;
                        }
                        reader.Close();
                        dbParams.Clear();
                    }
                    if(needStop)
                        break;
                }
                if(dbParams.Count > 0) {
                    var command = new SqlCommand(string.Format(sqlQuery, string.Join(",", sqlParams.Take(dbParams.Count))), conn, null);
                    command.Parameters.AddRange(dbParams.ToArray());
                    var reader = command.ExecuteReader();
                    var result = new string[reader.FieldCount];
                    while(reader.Read()) {
                        for(var index = 0; index < reader.FieldCount; index++)
                            result[index] = reader[index].ToString();
                        if(func(result))
                            break;
                    }
                    reader.Close();
                }
            }
        }

        public override DataTable GetTableSchema(string tableName) {
            using(var connect = new SqlConnection(connectionString)) {
                connect.Open();
                var adapter = new SqlDataAdapter("select * from " + tableName, connect);
                var dt = new DataTable();
                return adapter.FillSchema(dt, SchemaType.Mapped);
            }
        }

        public override void GetTableSchema(string tableName, out List<string> notNullableFields, out Dictionary<string, int> fieldLenght) {
            notNullableFields = new List<string>();
            fieldLenght = new Dictionary<string, int>();
            using(var connect = new SqlConnection(connectionString)) {
                connect.Open();
                var reader = new SqlCommand(string.Format("select name, system_type_id, max_length, is_nullable from sys.columns where OBJECT_ID = object_id('{0}')", tableName), connect).ExecuteReader();
                while(reader.Read()) {
                    if(reader[1].ToString() == "167")
                        fieldLenght.Add(reader[0].ToString().ToUpper(), int.Parse(reader[2].ToString()));
                    if(!bool.Parse(reader[3].ToString()))
                        notNullableFields.Add(reader[0].ToString().ToUpper());
                }
            }
        }

        public override long ExecuteId(string sql, DbTransaction ts, SqlParameter[] sqlParameters) {
            var sqlConnection = CreateDbConnection() as SqlConnection;
            var transaction = ts as SqlTransaction;
            if(sqlConnection == null || transaction == null)
                return 0;
            var com = sqlConnection.CreateCommand();
            com.Transaction = transaction;
            com.CommandText = sql + ";select  SCOPE_IDENTITY()";

            if(sqlParameters != null)
                foreach(var t in sqlParameters)
                    com.Parameters.Add(t);

            var intId = Convert.ToInt64(com.ExecuteScalar());
            com.Parameters.Clear();
            com.Dispose();
            return intId;
        }

        public override void ExecuteNone(string sql, DbTransaction ts, SqlParameter[] sqlParameters) {
            var sqlConnection = CreateDbConnection() as SqlConnection;
            var transaction = ts as SqlTransaction;
            if(sqlConnection == null || transaction == null)
                return;
            var com = sqlConnection.CreateCommand();
            com.Transaction = transaction;
            com.CommandText = sql;

            if(sqlParameters != null)

                foreach(var t in sqlParameters)
                    com.Parameters.Add(t);

            com.ExecuteNonQuery();
            com.Parameters.Clear();
            com.Dispose();
        }

        public override void ExecuteNoQuerySql(string sql, DbTransaction ts) {
            var sqlConnection = CreateDbConnection() as SqlConnection;
            var transaction = ts as SqlTransaction;
            if(sqlConnection == null || transaction == null)
                return;
            var com = sqlConnection.CreateCommand();
            com.Transaction = transaction;
            com.CommandText = sql;
            com.ExecuteNonQuery();
            com.Dispose();
        }

        public override DataTable ExecuteDataTable(string sql, DbTransaction ts) {
            var sqlConnection = CreateDbConnection() as SqlConnection;
            var transaction = ts as SqlTransaction;
            if(sqlConnection == null || transaction == null)
                return null;
            var com = sqlConnection.CreateCommand();
            com.CommandText = sql;
            com.Transaction = transaction;
            var sqlDataAdapter = new SqlDataAdapter(com);
            var dataTable = new DataTable();
            sqlDataAdapter.Fill(dataTable);
            sqlDataAdapter.Dispose();
            return dataTable;
        }

        public override void ExecuteNone(string sql, SqlParameter[] sqlParameters) {
            var conn = CreateDbConnection() as SqlConnection;
            if(conn == null)
                return;
            conn.Open();
            var com = conn.CreateCommand();
            com.CommandText = sql;

            foreach(var t in sqlParameters)
                com.Parameters.Add(t);

            com.ExecuteNonQuery();
            com.Parameters.Clear();
            com.Dispose();

            conn.Close();
            conn.Dispose();
        }

        public override DateTime GetDbTime() {
            using(var connect = new SqlConnection(connectionString)) {
                connect.Open();
                return DateTime.Parse(new SqlCommand("select GetDate()", connect).ExecuteScalar().ToString());
            }
        }
    }
}